package com.qunar.support.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;

import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;

import com.qunar.support.util.DBHelper;

/**
 * @author ziqiang.deng
 * 
 */
public class ApplyFormDao {
	static Logger logger = Logger.getLogger(ApplyFormDao.class);
	private Connection con;
	
	private static final int PAGESIZE = 20;
	
	/**
	 * 计算请求的条数
	 * @param uid 用户id
	 * @param filter 筛选框的内容，0代表全选，其它代表对应的status
	 * @return
	 * @author ziqiang.deng
	 */
	public int getVendorApplyNum(int uid, int filter) {
		String query = "select count(*) from applyform where apply_user_id=? and status=?";
		int number = -1;
		try {
			con = DBHelper.getConnection();
			PreparedStatement pstmt = con.prepareStatement( query );
			pstmt.setInt(1, uid);
			pstmt.setInt(2, filter);
			
			ResultSet rs = pstmt.executeQuery();
			while( rs.next() ){
				number = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return number;
	}

	/**
	 * 根据代理商和晒选条件选取apply，申请模块用到，对应需求6.4.1
	 * @param uid
	 * @param startIndex
	 * @param filter 筛选框的内容，0代表全选，其它代表对应的status
	 * @return
	 */
	public JSONArray getVendorApplyByPage(int uid, long startIndex, int filter) {
		String query = "SELECT title,content,arise_time,status FROM applyform WHERE apply_user_id = ? and status = ? limit ?,?";
		try {
			con = DBHelper.getConnection();
			PreparedStatement pstmt=con.prepareStatement(query);
			pstmt.setInt(1, uid);
			pstmt.setInt(2, filter);
			pstmt.setLong(3, startIndex);
			pstmt.setInt(4, PAGESIZE);
			ResultSet rs = pstmt.executeQuery();
			JSONArray arr=new JSONArray();
			while(rs.next()){
				JSONObject json = new JSONObject();
				json.put("title", rs.getString(1));
				json.put("content", rs.getString(2));
				json.put("arise_time", rs.getString(3));
				json.put("status", rs.getInt(4));
				arr.put(json);
			}
			return arr;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 审核人员选取申请列表，没有输入用户id时，对应需求6.5.1
	 * @param startIndex
	 * @param filter
	 * @return
	 * @author ziqiang.deng
	 */
	public JSONArray getVendorApplyByPage(long startIndex, int filter) {
		String query="SELECT login_name,title,content,arise_time,applyform.status FROM applyform,user applyform.check_user_id =? AND applyform.apply_user_id = user.id AND user.status != -1 AND applyform.status = ? limit ?,?";
		try {//....go on here
			con = DBHelper.getConnection();
			PreparedStatement pstmt=con.prepareStatement(query);
			pstmt.setInt(1, filter);
			pstmt.setLong(2, startIndex);
			pstmt.setInt(3, PAGESIZE);
			ResultSet rs=pstmt.executeQuery();
			JSONArray arr=new JSONArray();
			while(rs.next()){
				JSONObject json = new JSONObject();
				json.put("login_name", rs.getString(1));
				json.put("title", rs.getString(2));
				json.put("content", rs.getString(3));
				json.put("arise_time", rs.getString(4));
				json.put("status", rs.getInt(5));
				arr.put(json);
			}
			return arr;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 根据代理商选取其所有的apply，申请模块用到，对应需求6.4.1
	 * @param uid
	 * @param startIndex
	 * @return
	 * @author ziqiang.deng
	 */
	public JSONArray getVendorApplyByPage(int uid, long startIndex) {
		String query = "SELECT title,content,arise_time,status FROM applyform WHERE apply_user_id = ? limit ?,?";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = this.con.prepareStatement( query );
			pstmt.setInt(1, uid);
			pstmt.setLong(2, startIndex);
			pstmt.setInt(3, PAGESIZE);
			ResultSet rs = pstmt.executeQuery();
			JSONArray arr = new JSONArray();
			while( rs.next() ){
				JSONObject json = new JSONObject();
				json.put("title", rs.getString("title"));
				json.put("content", rs.getString("content"));
				json.put("arise_time", rs.getString("arise_time"));
				json.put("status", rs.getString("status"));
				arr.put(json);
			}
			
			return arr;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 审核人员选取申请列表，输入用户id时，对应需求6.5.1
	 * @param startIndex
	 * @return
	 * @author ziqiang.deng
	 */
	public JSONArray getVendorApplyByPage(long startIndex, String username, int check_user_id) {
		String query = "SELECT login_name,title,content,arise_time,applyform.status FROM applyform JOIN user on user.login_name = ? AND applyform.check_user_id = ? limit ?,?";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = this.con.prepareStatement(query);
			pstmt.setString(1, username);
			pstmt.setInt(2, check_user_id);
			pstmt.setLong(3, startIndex);
			pstmt.setInt(4, PAGESIZE);
			ResultSet rs = pstmt.executeQuery();
			JSONArray arr = new JSONArray();
			while( rs.next() ){
				JSONObject json = new JSONObject();
				json.put("login_name", rs.getString("login_name"));
				json.put("title", rs.getString("title"));
				json.put("content", rs.getString("content"));
				json.put("arise_time", rs.getString("arise_time"));
				json.put("status", rs.getString("status"));
				arr.put(json);
			}
			
			return arr;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 代理商发起一个新申请，写入applyform表，此时应该分配审核人员
	 * @param startIndex
	 * @return
	 * @author ziqiang.deng
	 */
	public void addNewApply(int uid, String title, String content) {
		String sql = "INSERT INTO applyform (apply_user_id, check_user_id, arise_time, deal_time, status, is_read, urgent, title, content)"
					+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = this.con.prepareStatement(sql);
			pstmt.setInt(1, uid);
			//从当前在线的审核人员中选择一个将该申请分配给他...
			pstmt.setInt(2, 0);
			long currentTime = System.currentTimeMillis();
			Time time = new Time( currentTime );
			Date date = new Date( currentTime );
			pstmt.setString(3, date.toString() + " " + time.toString());
			pstmt.setString(4, date.toString() + " " + time.toString());
			pstmt.setInt(5, 1);		//刚插入时状态为未审核，用status = 1表示
			pstmt.setInt(6, 0);		//刚插入时申请的发起者未读审核结果用read = 0表示
			pstmt.setInt(7, 0);		//默认urgent = 0表示非紧急申请
			pstmt.setString(8, title);
			pstmt.setString(9, content);
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ;
	}
	
	
	/**
	 * 操作员审核审核
	 * @param aid 审核的apply
	 * @param ouid 操作员id
	 * @param status 审核后的状态
	 * @author ziqiang.deng
	 */
	public void checkApply(int aid, int ouid, int status) {
		String sql = "UPDATE applyform SET check_user_id = ?, status = ?, deal_time = ? WHERE id = ?;";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = this.con.prepareStatement(sql);
			pstmt.setInt(1, ouid);
			pstmt.setInt(2, status);
			long currentTime = System.currentTimeMillis();
			Time time = new Time( currentTime );
			Date date = new Date( currentTime );
			pstmt.setString(3, date.toString() + " " + time.toString());
			pstmt.setInt(4, aid);
			
			pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ;
	}
	
	
}
